import os
import pandas as pd

def merge_eval_result(x1, x2, x3=None):

    x1 = pd.read_excel(x1)
    x2 = pd.read_excel(x2)
    x2_classes = list(x2['classes'])
    x1_classes = list(x1['classes'])

    tbar = enumerate(x1.iloc())
    x = {k: [] for k in ['classes', 'gts', 'dets1', 'dets2', 'true1', 'true2', 'recall1', 'recall2', 'precision1', 'precision2']}
    for i, r in tbar:
        # tbar.desc = '[%d/%d] %s' % (i+1, len(x1), r[0])
        x['classes'] += [r[0]]
        x['gts'] += [r[1]]
        x['dets1'] += [r[2]]
        x['true1'] += [r[3]]
        x['recall1'] += [r[4]]
        x['precision1'] += [r[5]]    
        if str(r[0]) in x2_classes:
            j = x2_classes.index(r[0])
            x['dets2'] += [x2['dets'][j]]
            x['true2'] += [x2['true'][j]]
            x['recall2'] += [x2['recall'][j]]
            x['precision2'] += [x2['precision'][j]]
        else:
            x['dets2'] += ['nan']
            x['true2'] += ['nan']
            x['recall2'] += ['nan']
            x['precision2'] += ['nan']
        
    tbar = enumerate(x2.iloc())    
    for i, r in tbar:
        # tbar.desc = '[%d/%d] %s' % (i+1, len(x2), r[0])
        if str(r[0]) in x['classes']:
            continue
        x['classes'] += [r[0]]
        x['gts'] += [r[1]]
        x['dets2'] += [r[2]]
        x['true2'] += [r[3]]
        x['recall2'] += [r[4]]
        x['precision2'] += [r[5]]  
        x['dets1'] += ['nan']
        x['true1'] += ['nan']
        x['recall1'] += ['nan']
        x['precision1'] += ['nan']    

    if x3 is not None:
        x3 = os.path.abspath(x3)
        if not os.path.exists(os.path.dirname(x3)):
            os.makedirs(os.path.dirname(x3))
        pd.DataFrame(x).to_excel(x3, index=False)
        print('Save result to', x3)

if __name__ == '__main__':
    x1 = r'G:\data\datasets\drink\cola\result_eval\coco(5)\swin_224_e60_s0.3_k10_bt-s0.1-cn.xlsx'
    x2 = r'G:\data\datasets\drink\cola\result_eval\coco(5)\swin_224_ft_e6_s0.3_k10_bt-s0.1.xlsx'
    x3 = r'G:\data\datasets\drink\cola\result_eval\coco(5)\predict.xlsx'    
    merge_eval_result(x1, x2, x3)    